﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web.Script.Services;
using System.Net.Mail;
using System.Net;
using Ebiz.libs;
using System.Globalization;

namespace Ebiz
{
    public partial class NewRequest : System.Web.UI.Page
    {
        public string TestString = "";
        public string DistributorList = "";
        public string ChannelList = "";
        public string InitDate = "";
        public enum ShowMode{
            _Customer, _Product
        }
        public enum EmailEngine {
            CDOSYS, NETMAIL
        }
        public enum EmailMode { 
            REQUEST, APPROVED, RETURNED, REJECTED
        }
        protected void Page_Load(object sender, EventArgs e){
            //dbName = DBUtilities.GetDBName("devCon");
            if (!IsPostBack){
                DistributorList = getDistributor();
                ChannelList = getChannel();

                string dtNow = DateTime.Today.ToString("yyyy/MM/dd");
                string dateNow = Func.FormatDate(dtNow, Func.DTFormat.YMDtoDMY, "", "-");
                InitDate = dateNow;
            }
        }

        protected static string getDistributor(){
            string result = "<option value=\"0\">--- SELECT ---</option>";

            string strCon = DBUtilities.strConnection("DevCon");
            string dbName = DBUtilities.GetDBName("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT DistributorCode, DistributorName " +
                    "FROM " + dbName + ".dbo.mstDistributor ORDER BY DistributorCode";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        string strCode = "<option value=\"" + sqlReader.GetValue(0).ToString() + "\">";
                        string strName = sqlReader.GetValue(1).ToString() + "</option>";

                        string opt = strCode + strName;
                        result += opt;
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        protected static string getChannel(){
            string result = "<option value=\"0\">--- SELECT ---</option>";

            string strCon = DBUtilities.strConnection("devCon");
            string dbName = DBUtilities.GetDBName("devCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT IDChannel, ChannelName FROM " + dbName + 
                    ".dbo.mstChannel WHERE " + 
                    "(StatusCode = '101') ORDER BY IDChannel";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        string strCode = "<option value=\"" + sqlReader.GetValue(0).ToString() + "\">";
                        string strName = sqlReader.GetValue(1).ToString() + "</option>";
                        string opt = strCode + strName;

                        result += opt;
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> GetRuleList(string RuleValue, string RuleValueExt){
            Dictionary<string, object> result = new Dictionary<string, object>();

            string[] RulePrefix = new string[3];
            RulePrefix[0] = "CD";
            RulePrefix[1] = "CH";
            RulePrefix[2] = "DC";

            string dbName = DBUtilities.GetDBName("devCon");

            string[] RuleQuery = new string[3];
            RuleQuery[0] = "SELECT COUNT(IDControl) FROM " + dbName + ".dbo.tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix[0].ToString() + "') AND " +
                "(Value1 = @Value1) AND (Value2 IS NOT NULL) AND (Value2 <> '')";
            RuleQuery[1] = "SELECT COUNT(IDControl) FROM " + dbName + ".dbo.tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix[1].ToString() + "') AND " +
                "(Value1 = @Value1)";
            RuleQuery[2] = "SELECT COUNT(IDControl) FROM " + dbName + ".dbo.tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix[2].ToString() + "')";

            int _ruleCount = 0;
            int RulePrefixLen = RulePrefix.Length;
            for (int idx = 0; idx < RulePrefixLen; idx++){
                switch (idx){
                    case 0:
                        double zroCD = 0;
                        if (Double.TryParse(RuleValueExt, out zroCD)){
                            if (double.Parse(RuleValueExt) >= 0){
                                Dictionary<string, object> lstParamsCD = new Dictionary<string, object>();
                                lstParamsCD.Add("@Value1", RuleValue);
                                _ruleCount = RuleCount(RuleQuery[idx], lstParamsCD);
                            }
                        }
                        break;
                    case 1:
                        Dictionary<string, object> lstParamsCH = new Dictionary<string, object>();
                        lstParamsCH.Add("@Value1", RuleValue);
                        _ruleCount = RuleCount(RuleQuery[idx], lstParamsCH);
                        break;
                    case 2:
                        double zroDC = 0;
                        if (Double.TryParse(RuleValueExt, out zroDC)){
                            if (double.Parse(RuleValueExt) >= 0){
                                _ruleCount = RuleCount(RuleQuery[idx]);
                            }
                        }
                        break;
                }

                if (_ruleCount > 0){
                    string xxx = "<script>alert('" + idx.ToString() + "');</script>";
                    string _MaxSteps = "0";
                    switch (idx){
                        case 0:
                            Dictionary<string, object> tmpResultCD = new Dictionary<string, object>();
                            tmpResultCD = CDCList(RulePrefix[0], RuleValue, RuleValueExt);
                            int _tmpMaxStepsCD = 0;
                            if (tmpResultCD["RuleExist"].ToString() == "1"){
                                _tmpMaxStepsCD = MaxSteps("CD", tmpResultCD["RuleCtrl"].ToString(), tmpResultCD["OptValue"].ToString(), tmpResultCD["OptValueExt"].ToString());
                                if (_tmpMaxStepsCD > 0){
                                    result = tmpResultCD;

                                    _MaxSteps = _tmpMaxStepsCD.ToString();
                                    result.Add("MaxSteps", _MaxSteps);
                                    result.Add("AppList", GetApprovalList(tmpResultCD["RuleCtrl"].ToString(), tmpResultCD["OptValue"].ToString(), tmpResultCD["OptValueExt"].ToString(), 1, ""));
                                }else{
                                    result["RuleExist"] = "0";
                                }
                            }else{
                                result["RuleExist"] = "0";
                            }
                            break;
                        case 1:
                            int _tmpMaxStepsCH = MaxSteps("CH", "CHAN", RuleValue, string.Empty);
                            if (_tmpMaxStepsCH > 0){
                                result.Add("RuleExist", "1");
                                result.Add("FixDiscount", "0");
                                result.Add("RuleCtrl", "CHAN");
                                result.Add("OptSymbol", "=");
                                result.Add("OptValue", RuleValue);
                                result.Add("OptValueReal", RuleValue);
                                result.Add("OptValueExt", string.Empty);

                                _MaxSteps = _tmpMaxStepsCH.ToString();
                                result.Add("MaxSteps", _MaxSteps);
                                result.Add("AppList", GetApprovalList("CHAN", RuleValue, string.Empty, 1, string.Empty));
                            }else{
                                result.Add("RuleExist", "0");
                            }
                            break;

                        case 2:
                            Dictionary<string, object> tmpResultDC = new Dictionary<string, object>();
                            tmpResultDC = CDCList(RulePrefix[2], RuleValueExt, string.Empty);

                            int _tmpMaxStepsDC = 0;
                            if (tmpResultDC["RuleExist"].ToString() == "1"){
                                _tmpMaxStepsDC = MaxSteps("DC", tmpResultDC["RuleCtrl"].ToString(), tmpResultDC["OptValue"].ToString(), tmpResultDC["OptValueExt"].ToString());
                                if (_tmpMaxStepsDC > 0){
                                    result = tmpResultDC;

                                    _MaxSteps = _tmpMaxStepsDC.ToString();
                                    result.Add("MaxSteps", _MaxSteps);
                                    result.Add("AppList", GetApprovalList(tmpResultDC["RuleCtrl"].ToString(), tmpResultDC["OptValue"].ToString(), string.Empty, 1, string.Empty));
                                }else{
                                    result["RuleExist"] = "0";
                                    result["Debug"] = tmpResultDC;
                                }
                            }else{
                                result["RuleExist"] = "0";
                                result["Debug"] = tmpResultDC;
                            }
                            break;
                    }
                    break;
                }else{
                    if (idx == (RulePrefixLen - 1)){
                        result.Add("RuleExist", "0");
                        result.Add("Debug", "NOEXIST");
                    }
                }
            }

            return result;
        }

        private static int RuleCount(string QueryString){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(QueryString, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string countRow = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(countRow);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static int RuleCount(string QueryString, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(QueryString, cn);
                foreach (KeyValuePair<string, object> dict in Params){
                    cmd.Parameters.AddWithValue(dict.Key, dict.Value);
                }
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string countRow = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(countRow);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static Dictionary<string, object> CDCList(string RulePrefix, string RuleValue, 
            string RuleValueExt){

            Dictionary<string, object> result = new Dictionary<string, object>();
            string qry = "";
            string dbName = DBUtilities.GetDBName("devCon");
            switch (RulePrefix){
                case "DC":
                    qry = "SELECT IDControl,  Operator1, Value1, FixDisc, Value1, Value2 " + 
                        "FROM " + dbName + ".dbo.tbl_ApprovalRule " +
                        "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix + "')";
                    break;
                case "CD":
                    qry = "SELECT IDControl, Operator2, Value2, FixDisc, Value1, Value2 " +
                        "FROM " + dbName + ".dbo. tbl_ApprovalRule " +
                        "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix + "') " + 
                        "AND (Value1 = '" + RuleValue + "')";
                    break;
            }

            double testValue = (RulePrefix == "DC") ? double.Parse(RuleValue) : double.Parse(RuleValueExt);
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string IDCtrl = rdr.GetValue(0).ToString();
                        string OptSym = rdr.GetValue(1).ToString();
                        string OptVal = rdr.GetValue(2).ToString();
                        string FixDisc = rdr.GetValue(3).ToString();
                        string CtrlValue = rdr.GetValue(4).ToString();
                        string OptValExt = rdr.GetValue(5).ToString();

                        bool isExist = isInRange(OptSym, testValue, double.Parse(OptVal));
                        if (isExist == true){
                            result.Add("RuleExist", "1");
                            result.Add("FixDiscount", FixDisc);
                            result.Add("RuleCtrl", IDCtrl);
                            result.Add("OptSymbol", OptSym);
                            string _optval = (RulePrefix == "DC") ? OptVal : CtrlValue;
                            result.Add("OptValue", _optval);
                            result.Add("OptValueReal", RuleValue);
                            result.Add("OptValueExt", OptValExt);

                            break;
                        }
                    }
                }else{
                   result.Add("RuleExist", "0");
                }
                cn.Close();

                //int resultLen = result.Count;
                //if (resultLen <= 0){
                //    result.Add("RuleExist", "0");
                //}
            }
            return result;
        }

        private static bool isInRange(string OptSymbol, double testRuleValue, double vsRuleValue){
            bool result = false;
            switch (OptSymbol){
                case "=":
                    result = (testRuleValue == vsRuleValue);
                    break;
                case "<":
                    result = (testRuleValue < vsRuleValue);
                    break;
                case ">":
                    result = (testRuleValue > vsRuleValue);
                    break;
                case "<=":
                    result = (testRuleValue <= vsRuleValue);
                    break;
                case ">=":
                    result = (testRuleValue >= vsRuleValue);
                    break;
            }
            return result;
        }

        private static int MaxSteps(string RulePrefix, string RuleControl, string RuleValue, 
            string RuleValueExt){

            int result = 0;
            string ruleVal = "";
            string ruleValExt = "";
            switch (RulePrefix){
                case "CH":
                    ruleVal = "'" + RuleValue + "'";
                    ruleValExt = "''";
                    break;
                case "CD":
                    ruleVal = "'" + RuleValue + "'";
                    ruleValExt = "'" + RuleValueExt + "'";
                    break;
                case "DC":
                    ruleVal = "'" + RuleValue + "'";
                    ruleValExt = "''";
                    break;
            }

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT ISNULL(MAX(StepSeq), 0) AS MaxSteps FROM " + 
                dbName + ".dbo.tbl_ApprovalStep WHERE " +
                "(IDControl = '" + RuleControl + "') AND (Value1 = " + ruleVal + ") AND " +
                "(Value2 = " + ruleValExt + ") AND (NextStep <> '0')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string rval = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(rval);
                    }
                }else{
                    result = 0;
                }
            }
            return result;
        }
        
        /*
        //TO DO: this function must be created as general query exec
        private static List<object> ExecQuery(string Query, Dictionary<string, object> Params){
            List<object> result = new List<object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(Query, cn);
                foreach (KeyValuePair<string, object> dict in Params){
                    cmd.Parameters.AddWithValue(dict.Key, dict.Value);
                }
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string strKey = rdr.GetValue(0).ToString();
                        string strVal = rdr.GetValue(2).ToString();

                        Dictionary<string, object> tmpResult = new Dictionary<string, object>();
                        tmpResult.Add(strKey, strVal);

                        result.Add(tmpResult);
                    }
                }
            }
            return result;
        }
        */

        private static Dictionary<string, string> AppStepList(string RuleControl, string RuleValue, 
            string RuleValueExt, int ApprovalLayer){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string dbName = DBUtilities.GetDBName("devCon");
            using (cn){
                cn.Open();

                string qry = "SELECT NextStep FROM " + dbName + ".dbo.tbl_ApprovalStep " +
                    "WHERE (IDControl = '" + RuleControl + "') AND (Value1 = '" + RuleValue + "') " +
                    "AND (Value2 = '" + RuleValueExt + "') " + "AND (StepSeq = " + ApprovalLayer + ") " +
                    "GROUP BY NextStep";
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }
                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static Dictionary<string, string> AppStepList(string RuleControl, string RuleValue, string RuleValueExt,
            int ApprovalLayer, string CurrentApproval){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string dbName = DBUtilities.GetDBName("devCon");
            using (cn){
                cn.Open();
                string xtra = " AND ((CurrentStep = '" + CurrentApproval + "') OR ";
                xtra += "(CurrentStep = CONVERT(NVARCHAR,(SELECT IDRole FROM " + dbName + ".dbo.UserAccounts WHERE UserName = '" + CurrentApproval + "')))) ";
                string qry = "SELECT NextStep FROM " + dbName + ".dbo.tbl_ApprovalStep " +
                    "WHERE (IDControl = '" + RuleControl + "') AND (Value1 = '" + RuleValue + "') " +
                    "AND (Value2 = '" + RuleValueExt + "') " + "AND (StepSeq = " + ApprovalLayer + ") " + 
                    xtra + "GROUP BY NextStep";

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }

                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> GetApprovalList(string RuleControl, string RuleValue, string RuleValueExt,
            int ApprovalLayer, string CurrentApproval){

            List<object> result = new List<Object>();
            string xs = "";
            Dictionary<string, string> dctAppStep = new Dictionary<string, string>();
            if (string.IsNullOrEmpty(CurrentApproval)){
                dctAppStep = AppStepList(RuleControl, RuleValue, RuleValueExt, ApprovalLayer);
            }else{
                dctAppStep = AppStepList(RuleControl, RuleValue, RuleValueExt, ApprovalLayer, CurrentApproval);
            }

            string sRole = "";
            string sUname = "";
            foreach (KeyValuePair<string, string> oAppList in dctAppStep){
                if (oAppList.Key == "ROLE"){
                    sRole = oAppList.Value;
                }else if (oAppList.Key == "UNAME"){
                    sUname = oAppList.Value;
                }else{
                    xs = oAppList.Value;
                }
            }

            string xRole = (string.IsNullOrEmpty(sRole)) ? "'0'" : sRole;
            string xUname = (string.IsNullOrEmpty(sUname)) ? "''" : sUname;

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT UserName, FullName " +
                "FROM " + dbName + ".dbo.UserAccounts WHERE (AppActor = 'Y') AND (StatusCode = '101') AND " +
                "((IDRole IN (" + xRole + ")) OR " +
                "(UserName IN (" + xUname + "))) " +
                "ORDER BY FullName";
            
            result = AppUserList(qry);
            return result;
        }

        private static List<object> AppUserList(string Query){
            List<object> result = new List<object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(Query, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string dctKey = rdr.GetValue(0).ToString();
                        string dctVal = rdr.GetValue(1).ToString();

                        Dictionary<string, object> dctResult = new Dictionary<string, object>();
                        dctResult.Add("appKey", dctKey);
                        dctResult.Add("appVal", dctVal);

                        result.Add(dctResult);
                    }
                }
                cn.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> ShowDataList(string ID, string Name,
            int StartIndex, ShowMode showMode){
                try
                {
                    int SearchMode = 0;
                    Dictionary<string, object> result = new Dictionary<string, object>();

                    if (!(Name == string.Empty))
                    {
                        int TotalRows = 0;
                        string TableName = "";
                        string FieldName = "";
                        string ProcedureName = "";
                        switch (showMode)
                        {
                            case ShowMode._Customer:
                                TableName = "v_mapCustomer";
                                FieldName = "CUSTNAME";
                                ProcedureName = "CustomerList";
                                SearchMode = 0;
                                break;
                            case ShowMode._Product:
                                TableName = "v_mapProduct";
                                FieldName = "ProductName";
                                ProcedureName = "ProductList";
                                SearchMode = 1;
                                break;
                        }

                        SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
                        string dbName = DBUtilities.GetDBName("devCon");

                        string strQry = "SELECT COUNT(DistributorCode) AS TotalRows FROM " + TableName +
                            " WHERE (DistributorCode = '" + ID + "') AND (" + FieldName + " LIKE '%" +
                            Name + "%')";
                        using (sqlCon)
                        {
                            sqlCon.Open();

                            SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                            SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                            if (sqlRdr.HasRows)
                            {
                                if (sqlRdr.Read())
                                {
                                    TotalRows = Convert.ToInt32(sqlRdr.GetValue(0).ToString());
                                }
                            }
                            sqlCon.Close();
                        }

                        if (TotalRows > 0)
                        {
                            List<string> SearchKey = new List<string>();
                            SearchKey.Add(ID);
                            SearchKey.Add(Name);

                            result = GetDataList(StartIndex, TotalRows, ProcedureName, SearchKey, SearchMode);
                        }
                    }
                    return result;
                }
                catch (Exception ex)
                {
                    Dictionary<string, object> result = new Dictionary<string, object>();
                    return result;
                }
        }

        private static Dictionary<string, object> GetDataList(int StartIndex, int TotalRows,
            string ProcedurName, List<string> SearchKey, int SearchMode){

            Dictionary<string, object> result = new Dictionary<string, object>();

            int ShowRows = 15;
            int NumRows = TotalRows;
            int TPages = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(NumRows / ShowRows)));

            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                List<object> TblRows = new List<object>();
                int StartOffset = (StartIndex < 2) ? 0 : ((StartIndex * ShowRows) - ShowRows) + 1;
                int EndOffset = (StartIndex > 1) ? (ShowRows - 1) : ShowRows;

                if (NumRows > 0){
                    sqlCon.Open();
                    SqlCommand sqlCmd = new SqlCommand(ProcedurName, sqlCon);
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.Add(new SqlParameter("Start", StartOffset));
                    sqlCmd.Parameters.Add(new SqlParameter("ShowRows", EndOffset));
                    sqlCmd.Parameters.Add(new SqlParameter("ID", SearchKey[0]));
                    sqlCmd.Parameters.Add(new SqlParameter("Name", SearchKey[1]));

                    SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                    if (sqlRdr.HasRows){
                        int idx = 0;
                        while (sqlRdr.Read()){
                            string tmpResult1 = sqlRdr.GetValue(1).ToString();
                            string tmpResult2 = sqlRdr.GetValue(2).ToString();
                            string tmpResult3 = sqlRdr.GetValue(3).ToString();
                            string tmpResult4 = "";
                            if (SearchMode == 0) {
                                tmpResult4 = sqlRdr.GetValue(4).ToString();
                            }

                            Dictionary<string, object> tblRow = new Dictionary<string, object>();
                            if (SearchMode == 0) {
                                tblRow.Add("ID", tmpResult1);
                                tblRow.Add("IDAzi", tmpResult2);
                                tblRow.Add("Name", tmpResult3);
                                tblRow.Add("Address", tmpResult4);
                            }else if (SearchMode == 1){
                                tblRow.Add("ID", tmpResult1);
                                tblRow.Add("Name", tmpResult2);
                            }
                            tblRow.Add("Distributor", tmpResult3);
                            TblRows.Add(tblRow);

                            idx++;
                        }
                    }
                    sqlCon.Close();

                    int ActivePage = (StartIndex == 0) ? 1 : StartIndex;
                    int PrevPage = (ActivePage > 1) ? (ActivePage - 1) : 0;
                    int NextPage = (ActivePage < TPages) ? (ActivePage + 1) : 0;
                    int TotalPages = (TPages > 1) ? TPages : 1;

                    result.Add("ShowRows", ShowRows);
                    result.Add("PrevPage", PrevPage);
                    result.Add("ActivePage", ActivePage);
                    result.Add("NextPage", NextPage);
                    result.Add("TotalPages", TotalPages);
                    result.Add("Content", TblRows);
                }
            }
            return result;
        }

        private static string GenerateID(string IDChannel){
            string result;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string dbName = DBUtilities.GetDBName("devCon");
            using (cn){
                string thisYear = string.Format(DateTime.Now.ToString("yy"));
                string strQuery = "SELECT {fn IFNULL(MAX(CAST(SUBSTRING(CLNum, 12, 3) AS Integer)), 0)} AS CLNumber " +
                    "FROM " + dbName + ".dbo.tbl_Request WHERE (Channel = '" + IDChannel + "') " +
                    "AND (SUBSTRING(CLNum, 9, 2) = '" + thisYear + "')";
                SqlCommand sqlCmd = new SqlCommand(strQuery, cn);
                cn.Open();

                int LastSeq = 0;
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    if (sqlReader.Read()){
                        LastSeq = Convert.ToInt32(sqlReader.GetValue(0).ToString());
                    }
                }else{
                    LastSeq = 0;
                }
                cn.Close();

                string NewSeq = (LastSeq + 1).ToString();
                NewSeq = NewSeq.PadLeft(3, '0');
                string YearID = DateTime.Now.Year.ToString();
                YearID = YearID.Substring(2, 2);

                result = "AZI/" + IDChannel.ToUpper() + "/" + thisYear + "/" + NewSeq;
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> SaveNewRequest(string Distributor, string DiscountType, string Channel, 
            string CLCustName, string CustGroup, string Reason, string DateStart, string DateEnd,
            string PrevCL, string RuleCtrl, string RuleVal, string RuleValExt, string CurrentStep,
            string NextStep, int ApprovalStep, int ApprovalMax, string ProductDivision){
            
            List<object> result = new List<object>();
            string CLNum = GenerateID(Channel);
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                cn.Open();

                string qry = "INSERT INTO tbl_Request (CLNum, PrevCL, DistributorCode, CLCustName, " +
                    "CustomerGroup, Channel, DateRequest, DateStart, DateEnd, DiscType, DiscReason, " +
                    "Token, TokenExpiry, IDControl, Value1, Value2, ApprovalCurrent, ApprovalNext, " +
                    "ApprovalStep, ApprovalMax, CreatedBy, CreatedDate, ProdDivision, StatusCode) VALUES " +
                    "(@CLNum, @PrevCL, @DistributorCode, @CLCustName, " +
                    "@CustomerGroup, @Channel, GetDate(), @DateStart, @DateEnd, @DiscType, @DiscReason, " +
                    "@Token, @TokenExpiry, @IDControl, @Value1, @Value2, @ApprovalCurrent, @ApprovalNext, " +
                    "@ApprovalStep, @ApprovalMax, @CreatedBy, GetDate(), @ProdDivision, @StatusCode)";

                //string DateRequest = DateTimeOffset.Now.Date.ToString("yyyy/MM/dd HH:mm:ss");
                string _DateStart = Func.FormatDate(DateStart, Func.DTFormat.DMYtoYMD, "-", "/");
                string _DateEnd = Func.FormatDate(DateEnd, Func.DTFormat.DMYtoYMD, "-", "/");
                string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);
                string Token = Func.GetUserInfo(Func.ShowInfo._SSID);

                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@CLNum", CLNum);
                cmd.Parameters.AddWithValue("@PrevCL", PrevCL);
                cmd.Parameters.AddWithValue("@DistributorCode", Distributor);
                cmd.Parameters.AddWithValue("@CLCustName", CLCustName);
                cmd.Parameters.AddWithValue("@CustomerGroup", CustGroup);
                cmd.Parameters.AddWithValue("@Channel", Channel);
                //cmd.Parameters.AddWithValue("@DateRequest", DateRequest);
                cmd.Parameters.AddWithValue("@DateStart", _DateStart);
                cmd.Parameters.AddWithValue("@DateEnd", _DateEnd);
                cmd.Parameters.AddWithValue("@DiscType", DiscountType);
                cmd.Parameters.AddWithValue("@DiscReason", Reason);
                cmd.Parameters.AddWithValue("@Token", Token);
                                
                cmd.Parameters.AddWithValue("@TokenExpiry", DateTime.Today.AddDays(7).ToString("yyyy/MM/dd hh:mm:ss"));
                cmd.Parameters.AddWithValue("@IDControl", RuleCtrl);
                cmd.Parameters.AddWithValue("@Value1", RuleVal);
                cmd.Parameters.AddWithValue("@Value2", RuleValExt);
                cmd.Parameters.AddWithValue("@ApprovalCurrent", CurrentStep);
                cmd.Parameters.AddWithValue("@ApprovalNext", NextStep);
                cmd.Parameters.AddWithValue("@ApprovalStep", ApprovalStep);
                cmd.Parameters.AddWithValue("@ApprovalMax", ApprovalMax);
                cmd.Parameters.AddWithValue("@CreatedBy", CreatedBy);
                //cmd.Parameters.AddWithValue("@CreatedDate", DateRequest);
                cmd.Parameters.AddWithValue("@ProdDivision", ProductDivision);
                cmd.Parameters.AddWithValue("@StatusCode", "200");

                try {
                    int qryExec = cmd.ExecuteNonQuery();
                    if (qryExec > 0){
                        result.Add(1);
                        result.Add(CLNum);
                        result.Add(Distributor);
                        result.Add(CustGroup);
                    }else{
                        result.Add(0);
                        result.Add("Update data failed!");
                    }
                }catch (Exception ex) {
                    string errMsg = ex.Message;
                    result.Add(0);
                    result.Add(errMsg);
                }
                cn.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string SaveCustomers(string ID, string Distributor, string CSGroup, 
            List<object> CSList) {

            string result = "";
            string dbName = DBUtilities.GetDBName("devCon");
            int csLen = CSList.Count;
            for (int idx = 0; idx < csLen; idx++) {
                Dictionary<string, object> dct = (Dictionary<string, object>)CSList[idx];
                string CSID = dct["CSID"].ToString();
                string CSIDAzi = dct["CSIDAzi"].ToString();
                string CSName = dct["CSName"].ToString();
                string CSAddress = dct["CSAddress"].ToString();

                string qry = "INSERT INTO " + dbName + ".dbo.tbl_RequestCustomer (CLNum, DistributorCode, " +
                    "CustGroup, CustCode, CSCodeAZI, CustName, CustAddress) " +
                    "VALUES (@CLNum, @DistributorCode, " +
                    "@CustGroup, @CustCode, @CSCodeAZI, @CustName, @CustAddress)";
                Dictionary<string, object> lstParams = new Dictionary<string, object>();
                lstParams.Add("@CLNum", ID);
                lstParams.Add("@DistributorCode", Distributor);
                lstParams.Add("@CustGroup", CSGroup);
                lstParams.Add("@CustCode", CSID);
                lstParams.Add("@CSCodeAZI", CSIDAzi);
                lstParams.Add("@CustName", CSName);
                lstParams.Add("@CustAddress", CSAddress);

                try{
                    int xqry = ExecQuery(qry, lstParams);
                    if (xqry < 1){
                        result = "failed";
                        //RollBack action here
                        int RollBack = RollBackUpdate(ID);
                        break;
                    }else if ((idx == (csLen - 1)) && !(result == "failed")){
                        result = "success";
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string SaveProducts(string ID, string Distributor, string ProductDivision, 
            List<object> ProductList){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            int ListLen = ProductList.Count;
            for (int idx = 0; idx < ListLen; idx++){
                Dictionary<string, object> dct = (Dictionary<string, object>)ProductList[idx];
                string ProductID = dct["ProdID"].ToString();
                //string ProductIDAzi = dct["ProdIDAzi"].ToString();
                string ProductName = dct["ProdName"].ToString();
                double ProductDisc = Convert.ToDouble(dct["ProdDisc"].ToString());
                int ProductQTY = Convert.ToInt32(dct["ProdQTY"].ToString());

                string qry = "INSERT INTO " + dbName + ".dbo.tbl_RequestProduct (CLNum, DistributorCode, " +
                    "ProductCode, ProdDivision, ProductName, Discount, Quantity) " + 
                    "VALUES (@CLNum, @DistributorCode, " +
                    "@ProductCode, @ProdDivision, @ProductName, @Discount, @Quantity)";

                string qryX = "INSERT INTO  " + dbName + ".dbo.tbl_RequestProduct (CLNum, DistributorCode, " +
                    "ProductCode, ProdDivision, ProductName, Discount, Quantity) " +
                    "VALUES ('" + ID + "', '" + Distributor + "', " +
                    "'" + ProductID + "', '" + ProductDivision + "', '" + ProductName + "', " + ProductDisc + ", " + ProductQTY + ")";

                Dictionary<string, object> lstParams = new Dictionary<string, object>();
                lstParams.Add("@CLNum", ID);
                lstParams.Add("@DistributorCode", Distributor);
                lstParams.Add("@ProductCode", ProductID);
                lstParams.Add("@ProdDivision", ProductDivision);
                lstParams.Add("@ProductName", ProductName);
                lstParams.Add("@Discount", ProductDisc);
                lstParams.Add("@Quantity", ProductQTY);

                if (ExecQuery(qry, lstParams) < 1){
                    //result = qryX;
                    result = "failed";
                    //RollBack action here
                    int RollBack = RollBackUpdate(ID);
                    break;
                }else if ((idx == (ListLen - 1)) && !(result == "failed")){
                    result = "success";
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> SaveApprovals(string ID, List<object> ApprovalList) {
            
            Dictionary<string, object> result = new Dictionary<string, object>();
            string Token = Func.GetUserInfo(Func.ShowInfo._SSID);

            Dictionary<string, object> dctSYS = Func.GetSysConfig();
            string _Port = dctSYS["ServerPort"].ToString();

            //string SYSPort = (string.IsNullOrEmpty(_Port) || _Port == "80") ? "/" : ":" + _Port + "/";
            string SYSPort = "/";
            string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;
            string PageQry = "id=" + ID + "&atdn=" + Token;
            string PageLink = SYSUrl + "Default.aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);

            string dbName = DBUtilities.GetDBName("devCon");
            int ListLen = ApprovalList.Count;
            for (int idx = 0; idx < ListLen; idx++) { 
                Dictionary<string, object> dct = (Dictionary<string, object>)ApprovalList[idx];
                string StepSeq = dct["StepNum"].ToString();
                string CurrentStep = dct["CStep"].ToString();
                string NextStep = dct["NStep"].ToString();
                string EmailAddress = GetEmail(CurrentStep);
                string EmailDate = "NULL";
                string TokenExpiry = "NULL";

                int EmailSent = 0;
                //string PageQry = "id=" + ID + "&atdn=" + Token;
                //string PageLink = "";
                if (idx == 0) {
                    //string PageQryMain = PageQry + "&user=" + CurrentStep + "&auto=true";
                    //PageLink = SYSUrl + "ViewRequest.aspx?cl=" + CryptLib.Base64(PageQryMain, CryptMode.ENCRYPT);

                    Dictionary<string, object> SendAppEmail = SendEmail(CurrentStep, ID, PageLink, EmailMode.REQUEST, EmailEngine.NETMAIL);
                    EmailSent = Convert.ToInt32(SendAppEmail["Status"]);
                    if (EmailSent == 0){
                        result["Status"] = 0;
                        result["Message"] = SendAppEmail["Message"];
                        
                        //RollBack action here
                        int RollBack = RollBackUpdate(ID);
                        break;
                    }else{
                        EmailDate = "'" + DateTime.Today.ToString("yyyy/MM/dd hh:mm:ss") + "'";
                        TokenExpiry = "'" + DateTime.Now.AddDays(7).ToString("yyyy/MM/dd hh:mm:ss") + "'";
                    }
                }

                string qry = "INSERT INTO " + dbName + ".dbo.tbl_RequestApproval (CLNum, StepSeq, CurrentStep, " +
                    "NextStep, EmailAddress, EmailDate, EmailSent, Token, TokenExpiry) VALUES ('" +
                    ID + "', " + StepSeq + ", '" + CurrentStep + "', '" + NextStep + "', '" +
                    EmailAddress + "', " + EmailDate + ", " + EmailSent + ", '" + Token + "', " +
                    TokenExpiry + ")";

                if (ExecQuery(qry) < 1){
                    result["Status"] = 0;
                    result["Message"] = "Update New Request Failed!";
                    
                    //RollBack action here
                    int RollBack = RollBackUpdate(ID);
                    break;
                }else if (idx == (ListLen - 1)){
                    result["Status"] = 1;
                    result["Message"] = "Update New Request Success!";
                }
            }
            return result;
        }

        private static string GetEmail(string PersonID) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT Email FROM " + dbName + ".dbo.UserAccounts WHERE (UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", PersonID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetFullName(string PersonID){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT FullName FROM " + dbName + ".dbo.UserAccounts WHERE (UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", PersonID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
        
        private static Dictionary<string, object> SendEmail(string MailTo, string CLNumber, string PageLink, 
            EmailMode SendMode, EmailEngine EmailCodeBy){

            Dictionary<string, object> result = new Dictionary<string, object>();
            
            string EmailAddress = GetEmail(MailTo);
            string EmailSubject = "BizCase request with CL Number " + CLNumber;
            if (!(string.IsNullOrEmpty(EmailAddress))){
                switch (EmailCodeBy){
                    case EmailEngine.CDOSYS:
                        //code for sending email via CDOSYS Mode
                        break;
                    case EmailEngine.NETMAIL:
                        Dictionary<string, object> dctConfig = GetEmailConfig();

                        MailMessage Mailer = new MailMessage();
                        Mailer.Subject = EmailSubject;
                        Mailer.From = new MailAddress(dctConfig["FromEmail"].ToString(), dctConfig["FromName"].ToString());
                        Mailer.Priority = (MailPriority)Convert.ToInt32(dctConfig["Priority"]);
                        Mailer.IsBodyHtml = (bool)Convert.ToBoolean(dctConfig["IsBodyHTML"]);

                        Mailer.To.Add(EmailAddress);
                        Mailer.CC.Add(GetEmail(Func.GetUserInfo(Func.ShowInfo._UserID)));

                        Dictionary<string, string> dctEmail = new Dictionary<string, string>();
                        dctEmail["Subject"] = EmailSubject;
                        dctEmail["Requester"] = Func.GetUserInfo(Func.ShowInfo._FullName);
                        dctEmail["AppvPerson"] = GetFullName(MailTo);
                        dctEmail["AppvPersonExt"] = "";
                        dctEmail["CLNum"] = CLNumber;
                        dctEmail["Link"] = PageLink;

                        Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.REQUEST);

                        SmtpClient _SMTP = new SmtpClient();
                        _SMTP.DeliveryMethod = (SmtpDeliveryMethod)Convert.ToInt32(dctConfig["DeliveryMethod"]);
                        _SMTP.UseDefaultCredentials = (bool)Convert.ToBoolean(dctConfig["DefaultCredentials"]);
                        if (!(dctConfig["UserName"] == null && dctConfig["Password"] == null)){
                            _SMTP.Credentials = new NetworkCredential(dctConfig["UserName"].ToString(), dctConfig["Password"].ToString());
                        }
                        //_SMTP.Credentials = new NetworkCredential(dctConfig["UserName"].ToString(), dctConfig["Password"].ToString());
                        _SMTP.Host = (string)dctConfig["Host"].ToString();
                        _SMTP.Port = (int)Convert.ToInt32(dctConfig["Port"]);
                        _SMTP.EnableSsl = (bool)Convert.ToBoolean(dctConfig["EnableSSL"]);
                        _SMTP.Timeout = (int)Convert.ToInt32(dctConfig["Timeout"]);

                        try{
                            _SMTP.Send(Mailer);
                            result["Status"] = 1;
                            result["Message"] = "Email Sent!";
                        }catch (Exception ex){
                            string ErrMsg = ex.Message;
                            result["Status"] = 0;
                            result["Message"] = ErrMsg;
                        }
                        break;
                }
            }else{
                result["Status"] = 0;
                result["Message"] = "Email address is empty!";
            }
            return result;
        }

        private static Dictionary<string, object> GetEmailConfig() {
            Dictionary<string, object> result = new Dictionary<string, object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                string qry = "SELECT ConfigName, ConfigValue, Description FROM mstConfig WHERE " +
                    "ConfigGroup = 'Email'";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string CFGName = rdr.GetValue(0).ToString();
                            string CFGValue = rdr.GetValue(1).ToString();
                            string CFGDesc = rdr.GetValue(2).ToString();

                            result.Add(CFGName, CFGValue);
                            /*
                            result.Add("CFGName", CFGName);
                            result.Add("CFGValue", CFGValue);
                            result.Add("CFGDesc", CFGDesc);
                            */
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string EmailMessage(Dictionary<string, string> MailDetails, EmailMode SendMode) {
            string Requester = MailDetails["Requester"];
            string AppvPerson = MailDetails["AppvPerson"];
            string AppvPersonExt = MailDetails["AppvPersonExt"];
            string CLNum = MailDetails["CLNum"];
            string PageLink = MailDetails["Link"];


            string result = "<html>";
            result += "<head></head>";
            result += "<body>";

            switch (SendMode) { 
                case EmailMode.REQUEST:
                    result += "<span>Dear " + AppvPerson + ",</span>";
                    result += "<p>" + Requester + " ";
                    result += "has requested a sales discount program through Ebiz System ";
                    result += "with CL Number <b>";
                    result += CLNum + "</b>, ";
                    result += "and await for your review and approval on this request.</p>";;
                    break;
                case EmailMode.APPROVED:
                    result += "<span>Dear " + Requester + ",</span>";
                    result += "<p>Your BizCase request with CL Number ";
                    result += CLNum + " has been <b>APPROVED</b></p>";
                    break;
            }
            result += "<p>Please click <a href=\"" + PageLink + "\"><b>here</b></a> ";
            result += "to review and approve the request.</p>";
            result += "Thank you and best regards,";
            result += "<p><b>Ebiz Admin</b></p>";
            result += "</body>";
            result += "</html>";
            return result;
        }

        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach(KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> AttachFiles(string ID, List<object> FileList) {
            Dictionary<string, object> result = new Dictionary<string, object>();
            
            //string FileLocation = HttpContext.Current.Server.MapPath("~/images/upload/");
            string FileLocation = "images/upload/";
            int ListLen = FileList.Count;
            string dbName = DBUtilities.GetDBName("devCon");
            for (var idx = 0; idx < ListLen; idx++) {
                Dictionary<string, object> dct = (Dictionary<string, object>)FileList[idx];
                string FileName = dct["FileName"].ToString();
                string[] _FileType = (dct["FileType"].ToString()).Split('.');
                string FileType = _FileType[1];
                string FileSize = dct["FileSize"].ToString();

                string qry = "INSERT INTO " + dbName + ".dbo.tbl_RequestFiles (CLNum, FileLocation, FileName, " +
                    "FileType, FileSize) VALUES " +
                    "(@CLNum, @FileLocation, @FileName, @FileType, @FileSize)";
                Dictionary<string, object> dctParams = new Dictionary<string, object>();
                dctParams.Add("@CLNum", ID);
                dctParams.Add("@FileLocation", FileLocation);
                dctParams.Add("@FileName", FileName);
                dctParams.Add("@FileType", FileType);
                dctParams.Add("@FileSize", FileSize);
                
                if (ExecQuery(qry, dctParams) < 1) {
                    result["Status"] = 0;
                    result["Message"] = "Update New Request Failed!";
                    result["Goto"] = "NewRequest.aspx";

                    //Rollback Action
                    int RollBack = RollBackUpdate(ID);
                    break;
                }else if (idx == (ListLen - 1)) {
                    string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
                    string PageLink = "Home.aspx";

                    result["Status"] = 1;
                    result["Message"] = "Update New Request Success!\nPlease check your email.";
                    result["Goto"] = PageLink;
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static int RollBackUpdate(string ID) {
            
            int result = 0;
            string qryWhere = "WHERE (CLNum = '" + ID + "')";

            string dbName = DBUtilities.GetDBName("devCon");
            List<string> qryCheck = new List<string>();
            qryCheck.Add("SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_RequestFiles " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_RequestApproval " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_RequestProduct " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_RequestCustomer " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_Request " + qryWhere);
            
            List<string> qryRollBack = new List<string>();
            qryRollBack.Add("DELETE FROM " + dbName + ".dbo.tbl_RequestFiles " + qryWhere);
            qryRollBack.Add("DELETE FROM " + dbName + ".dbo.tbl_RequestApproval " + qryWhere);
            qryRollBack.Add("DELETE FROM " + dbName + ".dbo.tbl_RequestProduct " + qryWhere);
            qryRollBack.Add("DELETE FROM " + dbName + ".dbo.tbl_RequestCustomer " + qryWhere);
            qryRollBack.Add("DELETE FROM " + dbName + ".dbo.tbl_Request " + qryWhere);

            int CountQry = qryCheck.Count;
            for(var idx = 0; idx < CountQry; idx++){
                int tmpResult = ExecQuery(qryRollBack[idx].ToString());
                //if(DataCount(qryCheck[idx].ToString()) > 0){
                //    int tmpResult = ExecQuery(qryRollBack[idx].ToString());
                //}
            }
            result = 0;
            return result;
        }

        private static int DataCount(string query) {
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try{
                    SqlCommand cmd = new SqlCommand(query, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) { 
                            result = Convert.ToInt32(rdr.GetValue(0));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
    }
}